package com.coalmine.api.util;


import cn.hutool.core.convert.Convert;
import cn.hutool.core.map.MapUtil;
import cn.hutool.core.util.StrUtil;
import com.alibaba.druid.pool.DruidPooledConnection;
import com.alibaba.fastjson.JSONObject;
import com.alibaba.fastjson.TypeReference;
import com.coalmine.api.common.QueryModelInfo;
import com.coalmine.api.domain.ApiConfig;
import com.coalmine.api.domain.ApiDatasource;
import com.coalmine.api.domain.ApiSql;
import com.coalmine.api.domain.ConnectionInfo;
import com.coalmine.common.exception.ServiceException;
import com.coalmine.common.utils.StringUtils;
import com.github.freakchick.orange.SqlMeta;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hpsf.Decimal;
import redis.clients.jedis.Jedis;

import java.sql.Date;
import java.sql.*;
import java.time.LocalDateTime;
import java.util.*;

import static java.util.stream.Collectors.collectingAndThen;
import static java.util.stream.Collectors.toCollection;


@Slf4j
public class JdbcUtil {

    public static ResultSet query(String sql, Connection connection) throws SQLException {
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        ResultSet resultSet = preparedStatement.executeQuery();
        return resultSet;
    }

    public static Connection getConnection(ApiDatasource ads) throws Exception {
        try {
            Class.forName(ads.getDriver());
            String password = ads.isEdit_password() ? ads.getPassword() : DESUtils.decrypt(ads.getPassword());
            Connection connection = null;
            if (StringUtils.equals("redis", ads.getType())) {
                if (StringUtils.equals(password, "0")) {
                    password = null;//密码为空
                }
                connection = DriverManager.getConnection(ads.getUrl(), null, password);
            } else {
                connection = DriverManager.getConnection(ads.getUrl(), ads.getUsername(), password);
            }
            log.info("successfully connected");
            return connection;
        } catch (ClassNotFoundException e) {
            throw new RuntimeException("Please check whether the jdbc driver jar is missing, if missed copy the jdbc jar file to lib dir. " + e.getMessage());
        }
    }

    /**
     * 查询库中所有表+表注释
     *
     * @param conn
     * @param sql
     * @return
     */
    public static Map<String,String> getAllTables(Connection conn, String sql) {

        Map<String,String> map = new HashMap<String,String>();
        PreparedStatement pst = null;
        try {
            pst = conn.prepareStatement(sql);
            ResultSet resultSet = pst.executeQuery();

            while (resultSet.next()) {
                String s = resultSet.getString(1);
                String s1 = resultSet.getString(2);

                map.put(s,s1);
            }
            return map;
        } catch (Exception e) {
            log.error(e.getMessage(), e);
            return null;
        } finally {
            try {
                if (pst != null)
                    pst.close();
                if (conn != null)
                    conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 查询表所有字段+备注
     *
     * @param conn
     * @param type
     * @param table
     * @return
     */
    public static List<JSONObject> getRDBMSColumnsPropertie(Connection conn, String type, String table) {
        String sql;
        switch (type) {
            case "postgresql":
                sql = "select * from \"" + table + "\" where 1=2";
                break;
            case "oracle":
                sql = "SELECT b.comments , a.column_name as name, a.data_type  as type " +
                        "FROM user_tab_columns a, user_col_comments b " +
                        "WHERE a.TABLE_NAME = '"+table+"' and b.table_name = '"+table+"' and a.column_name = b.column_name";
                break;
            case "sqlserver":
                sql = "SELECT t.[name] AS t_name,c.[name] AS name,tp.[name] AS type,cast(ep.[value] as varchar(100)) AS comment \n" +
                        "FROM sys.tables AS t INNER JOIN sys.columns \n" +
                        "AS c ON t.object_id = c.object_id LEFT JOIN sys.extended_properties AS  ep \n" +
                        "ON ep.major_id = c.object_id AND ep.minor_id = c.column_id  \n" +
                        "LEFT JOIN sys.types AS tp ON tp.user_type_id = c.user_type_id where t.name = '"+table+"'";
                break;
            case "clickhouse":
                sql = "desc " + table;
                break;
            default:
                sql = "select * from " + table + " where 1=2";
        }
        if(type.equals("oracle")){
            return getRDBMSColumnsPropertieOracle(conn, sql, table);
        }
        if( type.equals("clickhouse") || type.equals("sqlserver") ){
            return getRDBMSColumnsPropertieChAndSqlserver(conn, sql, table);
        }
        return getRDBMSColumnsPropertieCommon(conn, sql, table);
    }


    /**
     * 实际获取Oracle字段和字段描述
     * @param conn
     * @param sql
     * @param table
     * @return
     */
    public static List<JSONObject> getRDBMSColumnsPropertieOracle(Connection conn,  String sql, String table) {
        List<JSONObject> list = new ArrayList<>();
        PreparedStatement pst = null;
        try {
            log.info(sql);
            pst = conn.prepareStatement(sql);
            ResultSet rs = pst.executeQuery();
            while (rs.next()) {
                String fieldTypeName= rs.getString("name");
                String TypeName= rs.getString("type");
                String fieldJavaTypeName= rs.getString("type");
                //无法指定comment别名
                String columnRemark= rs.getString("comments");
                JSONObject jsonObject = new JSONObject();
                jsonObject.put("fieldTypeName", fieldTypeName);//数据库字段类型名
                jsonObject.put("fieldJavaTypeName", convertType(fieldJavaTypeName));//数据库字段类型 映射到java的类型名
                if (fieldTypeName.contains("."))
                    fieldTypeName = fieldTypeName.split("\\.")[1];
                jsonObject.put("label", fieldTypeName);//表字段名
                //表字段说明
                jsonObject.put("columnRemark", columnRemark);
                list.add(jsonObject);
            }
            return list;
        } catch (Exception e) {
            log.error(e.getMessage(), e);
            return null;
        } finally {
            try {
                if (pst != null)
                    pst.close();
                if (conn != null)
                    conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }


    /**
     * 实际获取clickhouse和sqlserver字段和字段描述
     * @param conn
     * @param sql
     * @param table
     * @return
     */
    public static List<JSONObject> getRDBMSColumnsPropertieChAndSqlserver(Connection conn,  String sql, String table) {
        List<JSONObject> list = new ArrayList<>();
        PreparedStatement pst = null;
        try {
            log.info(sql);
            pst = conn.prepareStatement(sql);
            ResultSet rs = pst.executeQuery();
            while (rs.next()) {
                String fieldTypeName= rs.getString("name");
                String TypeName= rs.getString("type");
                String fieldJavaTypeName= rs.getString("type");
                String columnRemark= rs.getString("comment");
                JSONObject jsonObject = new JSONObject();
                jsonObject.put("fieldTypeName", fieldTypeName);//数据库字段类型名
                jsonObject.put("fieldJavaTypeName", convertType(fieldJavaTypeName));//数据库字段类型映射到java的类型名
                if (fieldTypeName.contains("."))
                    fieldTypeName = fieldTypeName.split("\\.")[1];
                jsonObject.put("label", fieldTypeName);//表字段
                //表字段说明
                jsonObject.put("columnRemark", columnRemark);
                list.add(jsonObject);
            }
            return list;
        } catch (Exception e) {
            log.error(e.getMessage(), e);
            return null;
        } finally {
            try {
                if (pst != null)
                    pst.close();
                if (conn != null)
                    conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 实际获取非clickhouse字段和字段描述
     * @param conn
     * @param sql
     * @param table
     * @return
     */
    public static List<JSONObject> getRDBMSColumnsPropertieCommon(Connection conn, String sql, String table) {
        List<JSONObject> list = new ArrayList<>();
        ResultSet rs = null;
        try {
            log.info(sql);
            DatabaseMetaData metaData = conn.getMetaData();
            rs=metaData.getColumns(conn.getCatalog(), "%", table, "%");
            while (rs.next()) {
                String fieldTypeName= rs.getString(ResultSetColumnKeys.COLUMN_NAME.name());
                String TypeName= rs.getString(ResultSetColumnKeys.TYPE_NAME.name());
                String fieldJavaTypeName= rs.getString(ResultSetColumnKeys.TYPE_NAME.name());
                String columnRemark= rs.getString(ResultSetColumnKeys.REMARKS.name());
                JSONObject jsonObject = new JSONObject();
                jsonObject.put("fieldTypeName", fieldTypeName);//数据库字段类型名
                jsonObject.put("fieldJavaTypeName", convertType(fieldJavaTypeName));//数据库字段类型映射到java的类型名
                if (fieldTypeName.contains("."))
                    fieldTypeName = fieldTypeName.split("\\.")[1];
                jsonObject.put("label", fieldTypeName);//表字段
                //表字段说明
                jsonObject.put("columnRemark", columnRemark);
                list.add(jsonObject);
            }
            return list;
        } catch (Exception e) {
            log.error(e.getMessage(), e);
            return null;
        } finally {
            try {
                if (rs != null)
                    rs.close();
                if (conn != null)
                    conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }


    /**
     * 没有关闭连接，需要在调用方关闭
     *
     * @param connection
     * @param sql
     * @param jdbcParamValues
     * @return
     */
    public static Object executeSql(Connection connection, String sql, List<Object> jdbcParamValues) throws SQLException {
        //log.debug("sql===" + sql);
        //log.debug("params===" + JSON.toJSONString(jdbcParamValues));
        PreparedStatement statement = connection.prepareStatement(sql);
        //参数注入
        for (int i = 1; i <= jdbcParamValues.size(); i++) {
            statement.setObject(i, jdbcParamValues.get(i - 1));
        }
        boolean hasResultSet = statement.execute();
        if (hasResultSet) {
            ResultSet rs = statement.getResultSet();
            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();
            List<String> columns = new ArrayList<>();
            for (int i = 1; i <= columnCount; i++) {
                String columnName = metaData.getColumnLabel(i);
                columns.add(columnName);
            }
            List<JSONObject> list = new ArrayList<>();
            while (rs.next()) {
                JSONObject jo = new JSONObject();
                columns.stream().forEach(t -> {
                    try {
                        Object value = rs.getObject(t);
                        if (value != null && value instanceof Float || value instanceof Double
                                || value instanceof Decimal) {
                            //value = rs.getBigDecimal(t);
                            value = rs.getBigDecimal(t).toPlainString();
                            value = Convert.toBigDecimal(value);
                        }
                        if (value != null && value instanceof Timestamp || value instanceof Date || value instanceof LocalDateTime) {
                            value = parseDate(value);
                        }
                        jo.put(t, value);
                    } catch (SQLException throwables) {
                        throwables.printStackTrace();
                    }
                });
                list.add(jo);
            }
            return list;
        } else {
            int updateCount = statement.getUpdateCount();
            return updateCount + " rows affected";
        }
    }

    //解析对象到日期
    public static String parseDate(Object obj) {
        String format = "yyyy-MM-dd HH:mm:ss";
        if (obj == null) {
            return null;
        }
        String dateTimeStr = JSONObject.toJSONStringWithDateFormat(obj, format);
        return dateTimeStr.replaceAll("\"", "");
        // return obj.toString();
    }

    /**
     * 将获取的数据库类型，转换成java类型
     *
     * @param type
     * @return
     */
    public static String convertType(String type) {
        if (StrUtil.equals(type.toLowerCase(), "bigint")) {
            return "Long";
        }
        if (StrUtil.equals(type.toLowerCase(), "int") || StrUtil.equals(type.toLowerCase(), "tinyint")) {
            return "Integer";
        }
        if (StrUtil.equals(type.toLowerCase(), "string") || StrUtil.equals(type.toLowerCase(), "varchar") || StrUtil.equals(type.toLowerCase(), "text")) {
            return "String";
        }
        if (StrUtil.equals(type.toLowerCase(), "double")) {
            return "Double";
        }
        if (StrUtil.equals(type.toLowerCase(), "float") || StrUtil.equals(type.toLowerCase(), "float")) {
            return "Float";
        }
        if (StrUtil.equals(type.toLowerCase(), "decimal")) {
            return "Decimal";
        }
        if (StrUtil.equals(type.toLowerCase(), "timestamp")) {
            return "Timestamp";
        }
        if (StrUtil.equals(type.toLowerCase(), "datetime")) {
            return "DateTime";
        }
        if (StrUtil.equals(type.toLowerCase(), "date")) {
            return "Date";
        }
        if (StrUtil.equals(type.toLowerCase(), "boolean") || StrUtil.equals(type.toLowerCase(), "bool")) {
            return "Boolean";
        }
        return type;
    }

    /**
     * 获取自定义sql的结果集备注
     *
     * @param sql
     * @return
     */
    public static List<Map<String,String>> getSqlResult(ApiDatasource apiDatasource, String sql,Map<String, Object> sqlParam) throws SQLException {
        //封装返回结果
        List<Map<String,String>> resultList = new ArrayList<>();
        DruidPooledConnection conn = null;
        try {
            if(!StrUtil.equals(apiDatasource.getType(),"redis") ){
                //获取某个表所有字段的数据库连接
                conn = PoolManager.getPooledConnection(apiDatasource);
                //获取某个表所有字段名、类型、备注，获取sql中的单个表名
        //            List<JSONObject> columns=JdbcUtil.getRDBMSColumnsPropertie(conn, apiDatasource.getType(),getTableString(sql));
        //            //System.out.println("function e0========="+new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS").format(Calendar.getInstance().getTime()));
        //            for (JSONObject o: columns) {
        //                Map<String, String> stringStringMap = JSONObject.parseObject(o.toJSONString(), new TypeReference<Map<String, String>>() {});
        //                //System.out.println("columns="+stringStringMap);
        //                resultList.add(stringStringMap);
        //            }
                SqlMeta parse = null;
                //sql包含多表关联查询
                if(MapUtil.isNotEmpty(sqlParam)) {
                     parse = SqlEngineUtil.getEngine().parse(sql, sqlParam);
                }else {
                    parse = SqlEngineUtil.getEngine().parse(sql, new HashMap<>());
                }
                //System.out.println(parse.getSql());
                QueryModelInfo queryModelInfo = GetSQLTableName.getAllTableNameBySQL(parse.getSql());
                List<String> tableName = queryModelInfo.getTableName();
                for (String s:
                        tableName) {
                    List<JSONObject> columns = GetMultiTableComment.getRDBMSColumnsPropertie(conn, apiDatasource.getType(), s);
                    for (JSONObject o: columns) {
                        HashMap<String, String> stringStringMap = JSONObject.parseObject(o.toJSONString(), new TypeReference<HashMap<String, String>>() {});
                        //System.out.println("columns="+stringStringMap);
                        resultList.add(stringStringMap);
                    }
                }
            }
            else{
                Jedis jedis = RedisPoolManager.getRedisPooledConnection(apiDatasource);
                Set<String> set = jedis.keys("*");
                for (String s:set){
                    HashMap<String,String> newMap=new HashMap<>();
                    newMap.put("fieldTypeName",s);
                    newMap.put("fieldJavaTypeName","String");
                    newMap.put("columnRemark","redis中数据的key");
                    resultList.add(newMap);
                }
            }
            //去重
            resultList = resultList.stream().collect(
                    collectingAndThen(toCollection(() -> new TreeSet<>(Comparator.comparing( o -> o.get("fieldTypeName") ))),
                            ArrayList::new));
            return resultList;
        }catch (Exception e){
            throw new ServiceException("获取失败，请检查表是否存在！");
        }finally {
            if (conn != null)
                conn.close();
        }
    }



    /**
     * 获取自定义sql的结果集
     *
     * @param sql
     * @return
     */
    public static Object getSqlResultList(ApiDatasource apiDatasource, String sql,Map<String, Object> sqlParam) throws SQLException {
        //封装返回结果
        //List<Map<String,String>> resultList = new ArrayList<>();
        List<JSONObject> list = new ArrayList<>();
        DruidPooledConnection conn = null;
        PreparedStatement statement = null;
        ResultSet rs = null;
        try {
            if(!StrUtil.equals(apiDatasource.getType(),"redis") ){
                //获取某个表所有字段的数据库连接
                conn = PoolManager.getPooledConnection(apiDatasource);
                SqlMeta parse = null;
                //sql包含多表关联查询
                if(MapUtil.isNotEmpty(sqlParam)) {
                    parse = SqlEngineUtil.getEngine().parse(sql, sqlParam);
                }else {
                    parse = SqlEngineUtil.getEngine().parse(sql, new HashMap<>());
                }

                statement = conn.prepareStatement(parse.getSql());

                boolean hasResultSet = statement.execute();
                if (hasResultSet) {
                    rs = statement.getResultSet();
                    ResultSetMetaData metaData = rs.getMetaData();
                    int columnCount = metaData.getColumnCount();
                    List<String> columns = new ArrayList<>();
                    for (int i = 1; i <= columnCount; i++) {
                        String columnName = metaData.getColumnLabel(i);
                        columns.add(columnName);
                    }
                    while (rs.next()) {
                        JSONObject jo = new JSONObject();
                        ResultSet finalRs = rs;
                        columns.stream().forEach(t -> {
                            try {
                                Object value = finalRs.getObject(t);
                                if (value != null && value instanceof Float || value instanceof Double) {
                                    value = finalRs.getBigDecimal(t);
                                }
                                if (value != null && value instanceof Timestamp || value instanceof Date || value instanceof LocalDateTime) {
                                    value = parseDate(value);
                                }
                                jo.put(t, value);
                            } catch (SQLException throwables) {
                                throwables.printStackTrace();
                            }
                        });
                        list.add(jo);
                    }
                    return list;
                } else {
                    int updateCount = statement.getUpdateCount();
                    return updateCount + " rows affected";
                }
            }
        }catch (Exception e){
            throw new ServiceException("获取失败，请检查表是否存在！");
        }finally {
            if (rs != null)
                rs.close();
            if (statement != null)
                statement.close();
            if (conn != null)
                conn.close();
        }
        return  null;
    }

    
    /**
     * 获取单表查询sql的表名
     * @param sql
     * @return
     */
    private static String getTableString(String sql) {
        int index = sql.toLowerCase().indexOf("from");
        String table = sql.substring(index+4).trim();
        if(table.contains("where")){
            //"where所在位置"
            int whereIndex = table.toLowerCase().indexOf("where");
            //获取“where”
            String s = table.substring(whereIndex,whereIndex+5).trim();
            table = table.split(s)[0];
        }
        if(table.contains("\n")){
            table = sql.substring(index+4).trim().split("\n")[0];
        }
        if(table.contains(" ")){
            table = sql.substring(index+4).trim().split(" ")[0];
        }
        if(table.contains("\"")){
            table = table.replaceAll("\"","");
        }
        return table;
    }

    /**
     * 获取单表查询sql的子串，表名之前的sql语句
     * @param sql
     * @return
     */
    private static String getSqlString(String sql) {
        int index = sql.toLowerCase().indexOf("from");
        //select * from tableName
        String newSql = sql.substring(0,index+4).concat(" ").concat(getTableString(sql));
        return newSql;
    }

    /**
     * 传redis数据源和key，jdbc获取数据
     */
    public static List<Object> jdbcGetRedisObject(ApiDatasource ads,ApiConfig ac) {
        if( ads == null || ac == null){
            return null;
        }
        List<ApiSql> sqlList = ac.getSqlList();
        Connection con = null;
        try {
            //密码
            String pw = null;
            try {
                pw = DESUtils.decrypt(ads.getPassword());
            } catch (Exception e) {
                throw new RuntimeException(e.getMessage());
            }
            con = DriverManager.getConnection(ads.getUrl(),null,pw);
            Statement statement = con.createStatement();
            List<Object> list = new ArrayList<>();
            for (ApiSql apiSql : sqlList) {
                //SqlText样例："get key"
                System.out.println(apiSql.getSqlText());
                statement.execute(apiSql.getSqlText());
                ResultSet r = statement.getResultSet();
                int index = 0;
                Object o = null;
                while (r.next()) {
                    String result = r.getString(index);
                    if(StringUtils.isJson(result)){
                        o = JSONObject.parseObject(result);
                    }else{
                        o = result;
                    }
                    list.add(o);
                    index = index + 1;
                }
            }
            return list;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            throw new ServiceException("访问redis语法错误，或url为null");
        }finally {
            try {
                if (con != null)
                    con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 执行redis数据源api,jedis获取数据
     */
    public static List<Object> getRedisObject(ApiDatasource ads, ApiConfig ac)  {
        if( ads == null || ac == null){
           return null;
        }
        List<ApiSql> sqlList = ac.getSqlList();
        Jedis jedis = RedisPoolManager.getRedisPooledConnection(ads);
        List<Object> dataList = new ArrayList<>();
        Object o = null;
        //执行数据转换
        for (ApiSql apiSql : sqlList) {
            //获取redis中的数据。SqlText样例："key"
            String  value = jedis.get(apiSql.getSqlText());
            if(StringUtils.isJson(value)){
                o=JSONObject.parseObject(value);
            }else{
                o= value;
            }
            dataList.add(o);
        }
        return dataList;
    }

    public static ConnectionInfo parseJDBCUrl(String url) {
        String[] split = url.split(":");
        String host = org.apache.commons.lang3.StringUtils.replace(split[2],"//","");
        String[] portSplit = split[3].split("/");
        String port = portSplit[0];

        String[] databaseSplit = portSplit[1].split("\\?");
        String dbName = databaseSplit[0];

        ConnectionInfo connectionInfo = new ConnectionInfo();
        connectionInfo.setUrl(url);
        connectionInfo.setHost(host);
        connectionInfo.setPort(port);
        connectionInfo.setDbName(dbName);

        return connectionInfo;
    }

    public static void main(String[] args) {
        String url = "jdbc:clickhouse://192.168.0.131:8123/ods";
        System.out.println(parseJDBCUrl(url));
    }
}
